package com.clh.aligner.tools;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库结构校验工具类3.0
 * 项目启动时，将根据tableList，新增表或编辑表结构（包括主键、列和索引）；
 * 注意：1、对多余的表和字段不进行处理；2、无法修改库中的字段名
 */
@Component
public class DBAligner {

    @Autowired
    private DataSource dataSource;

    private static List<TableInfo> tableList = new ArrayList<>();

    private static List<String> sqlList = new ArrayList<>();

    {
        //示例表
        tableList.add(new TableInfo("test_db_init_example", "示范表")
                .keys("id")
                .columns(new TColumn("id", "int(11)", false, true, null, "主键列"),
                        new TColumn("name", "varchar(255)", true, false, null, "名称"),
                        new TColumn("number", "int(11)", true, false, null, "序号"),
                        new TColumn("delete_flag", "varchar(10)", true, false, "0", "删除标识"))
                .indexs(new TIndex("index_name", "name"),
                        new TIndex("index_number", "number"),
                        new TIndex("index_name_target", "name"),
                        new TIndex("index_name_number", "name", "number"))
        );



        //初始化id为1的记录（无则插入，有则跳过）
        sqlList.add(" INSERT INTO `test_db_init_example`(`id`, `name`, `number`, `delete_flag`) " +
                    " VALUES (1, '张三' , 3, '0'),(2, '李四' , 4, '0') " +
                    " ON DUPLICATE KEY UPDATE id=id ");
    }

    /**
     * ------------------------------ 下述代码为功能代码，不宜随意改动 ------------------------------
     */

    @PostConstruct
    public void check() {
        try {
            AbstractDBHelper dbHelper = getDBHelperByDataSource(dataSource);

            System.out.println("----------数据库结构校正-开始----------");
            dbHelper.tablesStructureAline(tableList);
            System.out.println("----------数据库结构校正-结束----------");

            System.out.println("----------执行自定义sql-开始----------");
            dbHelper.execSqlList(sqlList);
            System.out.println("----------执行自定义sql-结束----------");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过数据源，判断数据库类型等，选择合适的处理类
     */
    private AbstractDBHelper getDBHelperByDataSource(DataSource dataSource) {
        try (Connection conn = dataSource.getConnection()) {
            String databaseProductName = conn.getMetaData().getDatabaseProductName();
            //以mysql语法生成sql
            if ("mysql".equals(databaseProductName.toLowerCase())) {
                return new MysqlHelper(dataSource);
            }
            //可扩展其他sql语法
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //默认以mysql语法来生成sql
        return new MysqlHelper(dataSource);
    }

    /**
     * 常量类
     * */
    public interface DBConstants{
        //索引类型
        String INDEX_TYPE_NORMAL = "normal";
        String INDEX_TYPE_UNIQUE = "unique";
        String INDEX_DEFAULT_TYPE = INDEX_TYPE_NORMAL;
    }

    /**
     * 表结构
     */
    @Data
    @Accessors(chain = true)
    @NoArgsConstructor
    @AllArgsConstructor
    public class TableInfo {
        //表名
        String name;
        //表备注
        String comment;
        //列集
        List<TColumn> columnList = new ArrayList<>();
        //主键
        List<String> keyList = new ArrayList<>();
        //索引集
        List<TIndex> indexList = new ArrayList<>();

        TableInfo(String name, String comment) {
            this.name = name;
            this.comment = comment;
        }

        public TableInfo columns(TColumn... columns) {
            for (TColumn column : columns) {
                addColumn(column);
            }
            return this;
        }

        public TableInfo addColumn(TColumn column) {
            this.columnList.add(column);
            return this;
        }

        public TableInfo keys(String... keys) {
            this.keyList.addAll(Arrays.asList(keys));
            return this;
        }

        public TableInfo indexs(TIndex... indexs) {
            this.indexList.addAll(Arrays.asList(indexs));
            return this;
        }
    }

    /**
     * 字段结构
     */
    @Data
    @Accessors(chain = true)
    @NoArgsConstructor
    @AllArgsConstructor
    public class TColumn {
        String name;
        //字段类型，如varchar(30)
        String type;
        //是否允许空
        Boolean isNull;
        //是否自增
        Boolean isAutoIncrement;
        //默认值
        String defaultVal;
        //备注
        String comment;
    }


    /**
     * 索引结构
     * */
    @Data
    @Accessors(chain = true)
    @NoArgsConstructor
    @AllArgsConstructor
    public class TIndex {
        //索引名称
        String name;
        //类型 ，目前仅支持：唯一索引unique、正常索引normal
        String type = DBConstants.INDEX_DEFAULT_TYPE;
        //索引列表达式，例如： score默认为成绩升序；score DESC为成绩降序
        List<String> columnList =  new ArrayList<>();

        TIndex(String indexName, String... columnNames) {
            this.name = indexName;
            this.columnList.addAll(Arrays.asList(columnNames));
        }

        public TIndex addColumn(String columnName){
            columnList.add(columnName);
            return this;
        }
    }

    /**
     * 数据库操作基本要求
     */
    public interface DBHelperInterface {

        //读取表所有信息
        List<TableInfo> findTables() throws SQLException;

        //获取建表语句
        void doCreatTable(TableInfo table) throws SQLException;

        void modifyTableBaseInfo(TableInfo table) throws SQLException;

        //增加字段的DDL
        void doAddColumn(String tableName, TColumn column) throws SQLException;

        //修改字段的DDL
        void doModifyColumn(String tableName, TColumn column) throws SQLException;

        //删除主键
        void dropPrimaryKey(String tableName) throws SQLException;

        //添加主键
        void addPrimaryKey(String tableName, List<String> keys) throws SQLException;

        //删除索引
        void doDropIndex(String tableName, String indexName) throws SQLException;

        //增加字段的DDL
        void doAddIndex(String tableName, TIndex index) throws SQLException;

        //增加字段的DDL
        void doModifyIndex(String tableName, TIndex index) throws SQLException;
    }

    /**
     * 抽象的数据库操作类
     */
    public abstract class AbstractDBHelper implements DBHelperInterface {

        DataSource dataSource;

        Connection conn = null;

        Statement statement = null;

        List<TableInfo> oldTables = new ArrayList<>();

        AbstractDBHelper(DataSource dataSource) {
            this.dataSource = dataSource;
        }

        //初始化链接
        private void initDbConn() throws SQLException {
            if (this.conn == null || this.conn.isClosed()) {
                this.conn = dataSource.getConnection();
            }
            if (this.statement == null || this.statement.isClosed()) {
                this.statement = this.conn.createStatement();
            }
        }

        //日志打印
        void printLog(String content) {
            System.out.println(content);
        }

        //关闭连接
        private void closeConn() throws SQLException {
            if (statement != null) {
                statement.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

        //表结构是否一致
        boolean sameStructure(TableInfo a, TableInfo b){
            return equalsObj(a.getComment(), b.getComment());
        }

        //字段结构是否一致
        boolean sameStructure(TColumn a, TColumn b){
            return equalsObj(a.getType(), b.getType())
                    && equalsObj(a.getIsNull(), b.getIsNull())
                    && equalsObj(a.getIsAutoIncrement(), b.getIsAutoIncrement())
                    && equalsObj(a.getDefaultVal(), b.getDefaultVal())
                    && equalsObj(a.getComment(), b.getComment());
        }

        //索引结构是否一致
        boolean sameStructure(TIndex a, TIndex b){
            if (a.getColumnList().size() != b.getColumnList().size()) return false;
            if (!a.getType().equals(b.getType())) return false;
            for(int i = 0; i < b.getColumnList().size(); i++){
                if(!equalsObj(a.getColumnList().get(i), b.getColumnList().get(i))){
                    return false;
                }
            }
            return true;
        }

        //判断两Obj对象是否相等
        boolean equalsObj(Object s1, Object s2) {
            if (s1 == null) return s2 == null;
            return s1.equals(s2);
        }

        //获取当前库中的表结构
        void initOldTablesStructure() throws SQLException {
            this.oldTables = findTables();
        }

        //执行sql返回结果集
        List<Map> execQuerySQL(String sql) throws SQLException {
            ResultSet rs = statement.executeQuery(sql);
            List<Map> selectResult = new ArrayList();
            String[] colNames = getResultSetColumnNames(rs);
            int columnCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Map<String, String> recordInfo = new HashMap();
                for (int i = 0; i < columnCount; ++i)
                    recordInfo.put(colNames[i], rs.getString(colNames[i]));
                selectResult.add(recordInfo);
            }

            return selectResult;
        }

        //执行单条sql
        boolean executeSQL(String sql) throws SQLException {
            return statement.execute(sql);
        }

        //获取结果集中的列名
        private String[] getResultSetColumnNames(ResultSet rs) throws SQLException {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }
            return columnNames;
        }

        //根据表名获取库中表结构
        TableInfo getOldTable(String tableName) {
            return this.oldTables.stream().filter(t -> tableName.equals(t.getName())).findFirst().orElse(null);
        }

        //获取单字段结构
        TColumn getTableColumn(String tableName, String columnName) {
            TableInfo table = getOldTable(tableName);
            if (table == null || table.getColumnList() == null) return null;
            return table.getColumnList().stream().filter(c -> columnName.equals(c.getName())).findFirst().orElse(null);
        }

        //获取单索引结构
        TIndex getTableIndex(String tableName, String IndexName) {
            TableInfo table = getOldTable(tableName);
            if (table == null || table.getIndexList() == null) return null;
            return table.getIndexList().stream().filter(i -> IndexName.equals(i.getName())).findFirst().orElse(null);
        }

        //多个表结构校准
        public void tablesStructureAline(List<TableInfo> tableList) throws SQLException {
            //初始化连接
            initDbConn();
            //获取库中表结构
            initOldTablesStructure();

            for (TableInfo table : tableList) {
                TableInfo oldTable = getOldTable(table.getName());
                if (oldTable == null) {
                    printLog(String.format("创建表【%s】",table.getName()));
                    doCreatTable(table);
                } else {
                    tableStructureAline(table);
                }
            }
            closeConn();
        }

        //单个表结构校准
        public void tableStructureAline(TableInfo table) throws SQLException {
            //表基本信息校准
            baseInfoAline(table);

            //字段校准
            columnAline(table);

            //主键校准
            primaryKeyAline(table);

            //索引校准
            indexAline(table);
        }

        //表基本信息校准
        void baseInfoAline(TableInfo table) throws SQLException {
            TableInfo oldTable = getOldTable(table.getName());
            if (!sameStructure(table, oldTable)) {
                printLog(String.format("修改表【%s】备注", table.getName()));
                modifyTableBaseInfo(table);
            }
        }

        //表字段校准
        void columnAline(TableInfo table) throws SQLException {
            for (TColumn column : table.getColumnList()) {
                TColumn oldTableColumn = getTableColumn(table.getName(), column.getName());
                if (oldTableColumn == null) {
                    //不存在则添加
                    printLog(String.format("表【%s】添加字段【%s】", table.getName(), column.getName()));
                    doAddColumn(table.getName(), column);
                } else if (!sameStructure(column, oldTableColumn)) {
                    //存在且结构不同则修改
                    printLog(String.format("表【%s】修改字段【%s】", table.getName(), column.getName()));
                    doModifyColumn(table.getName(), column);
                }
            }
        }

        //主键校准
        void primaryKeyAline(TableInfo table) throws SQLException {
            TableInfo oldTable = getOldTable(table.getName());
            List<String> oldTableKeyNames = oldTable.getKeyList();
            List<String> newTableKeyNames = table.getKeyList();
            if (newTableKeyNames.isEmpty()) {
                //目前只对主键进行更换和新增，不处理删除，因此若主键为空则不进行操作
                return;
            }
            if (oldTableKeyNames.containsAll(newTableKeyNames) && oldTableKeyNames.size() == newTableKeyNames.size()) {
                //主键相同则无需操作
                return;
            }
            printLog(String.format("表【%s】更换主键: %s -> %s", table.getName(), oldTableKeyNames.toString(), newTableKeyNames.toString()));
            if (!oldTableKeyNames.isEmpty()) {
                dropPrimaryKey(table.getName());
            }
            if (!newTableKeyNames.isEmpty()) {
                addPrimaryKey(table.getName(), newTableKeyNames);
            }
        }

        //索引校准
        void indexAline(TableInfo table) throws SQLException {
            for(TIndex index : table.getIndexList()){
                TIndex oldTableIndex = getTableIndex(table.getName(), index.getName());
                if(oldTableIndex == null){
                    //不存在则添加
                    printLog(String.format("表【%s】添加索引【%s】，列：%s", table.getName(), index.getName(), index.getColumnList()));
                    doAddIndex(table.getName(), index);
                } else if(!sameStructure(index, oldTableIndex)){
                    printLog(String.format("表【%s】更新索引【%s】，列：%s -> %s ", table.getName(), index.getName(), oldTableIndex.getColumnList(), index.getColumnList()));
                    doModifyIndex(table.getName(), index);
                }
            }
        }

        //执行sql集合
        public void execSqlList(List<String> sqlList) throws SQLException {
            initDbConn();
            int i = 1;
            for (String sql : sqlList) {
                printLog(String.format("执行sql %s：%s", i++, sql));
                executeSQL(sql);
            }
            closeConn();
        }
    }

    /**
     * mysql实现类
     */
    public class MysqlHelper extends AbstractDBHelper {

        public MysqlHelper(DataSource dataSource) {
            super(dataSource);
        }

        //生成创建表的DLL语句
        private String getCreatTableDDL(TableInfo table) {

            String sqlTemplate = "CREATE TABLE IF NOT EXISTS %s (  %s  %s %s ) COMMENT='%s' ";

            String tableName = "`" + table.getName() + "`";

            String tableComment = table.getComment();

            String fieldsSql = null;
            StringBuilder fieldsSqlBuilder = new StringBuilder();
            for (TColumn column : table.getColumnList()) {
                fieldsSqlBuilder.append(", ").append(mysqlColumnDefine(column));
            }
            fieldsSql = fieldsSqlBuilder.substring(1);

            List<String> keyList = table.getKeyList();
            String primaryKeysSql = "";
            if (!CollectionUtils.isEmpty(keyList)) {
                primaryKeysSql = ",  PRIMARY KEY (`" + String.join("`, `", keyList) + "`) ";
            }

            List<TIndex> indexList = table.getIndexList();
            StringBuilder indexsSql = new StringBuilder();
            if(!CollectionUtils.isEmpty(indexList)){
                for(TIndex index : indexList){
                    indexsSql.append(", ").append(mysqlIndexDefine(index));
                }
            }

            return String.format(sqlTemplate, tableName, fieldsSql, primaryKeysSql, indexsSql.toString(), tableComment);
        }

        //获取mysql字段定义
        private String mysqlColumnDefine(TColumn column) {
            String mysqlDefine = "`" + column.getName() + "` " + column.getType() + (column.getIsNull() ? " NULL " : " NOT NULL ");
            mysqlDefine += !StringUtils.isEmpty(column.getComment()) ? " COMMENT '" + column.getComment() + "'" : " ";
            mysqlDefine += !StringUtils.isEmpty(column.getDefaultVal()) ? " DEFAULT '" + column.getDefaultVal() + "'" : " ";
            mysqlDefine += column.getIsAutoIncrement() ? " AUTO_INCREMENT " : "";
            return mysqlDefine;
        }

        //获取mysql索引定义
        private String mysqlIndexDefine(TIndex index) {

            String indexCols = String.join(", ", index.getColumnList());
            String indexType = index.getType();
            String indexDefine;
            switch (indexType){
                case DBConstants.INDEX_TYPE_NORMAL :
                    indexDefine = String.format("INDEX %s ( %s )", index.getName(), indexCols);
                    break;
                case DBConstants.INDEX_TYPE_UNIQUE :
                    indexDefine = String.format("UNIQUE INDEX ( %s )", indexCols);
                    break;
                default:
                    throw new IllegalArgumentException(String.format("mysql未适配[%s]索引类型: %s", index.getName(), indexType)) ;
            }

            return indexDefine;
        }

        private String getAddColumnDDL(String tableName, TColumn column) {
            return String.format(" ALTER TABLE `%s` add  %s ", tableName, mysqlColumnDefine(column));
        }

        private String getModifyColumnDDL(String tableName, TColumn column) {
            return String.format(" ALTER TABLE `%s` MODIFY  %s ", tableName, mysqlColumnDefine(column));
        }

        private String getCurrentDbName() throws SQLException {
            return execQuerySQL("select database() as db").get(0).get("db").toString();
        }

        @Override
        public List<TableInfo> findTables() throws SQLException {
            String dbName = getCurrentDbName();

            //表基本信息
            List<Map> tableBaseInfoMap = execQuerySQL(String.format(" SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES where TABLE_SCHEMA = '%s'", dbName));
            List<TableInfo> tables = tableBaseInfoMap.stream().map(m -> new TableInfo()
//                    .setDbName(dbName)
                            .setName((String) m.get("TABLE_NAME"))
                            .setComment((String) m.get("TABLE_COMMENT"))
            ).collect(Collectors.toList());

            //表字段集合
            for (TableInfo table : tables) {
                table.setColumnList(findOldColumns(dbName, table.getName()));
            }

            //主键
            for (TableInfo table : tables) {
                List<Map> keyMap = execQuerySQL(" SELECT COLUMN_NAME FROM information_schema.columns " +
                        "WHERE TABLE_SCHEMA='" + dbName + "' AND TABLE_NAME='" + table.getName() + "'  and COLUMN_KEY like '%PRI%' ");
                List<String> keyList = keyMap.stream().map(m -> (String) m.get("COLUMN_NAME")).collect(Collectors.toList());
                table.setKeyList(keyList);
            }

            //索引
            for (TableInfo table : tables) {
                table.setIndexList(findOldIndexs(dbName, table.getName()));
            }

            return tables;
        }


        @Override
        public void doCreatTable(TableInfo table) throws SQLException {
            executeSQL(getCreatTableDDL(table));
        }

        @Override
        public void modifyTableBaseInfo(TableInfo table) throws SQLException {
            executeSQL(String.format("ALTER TABLE `%s` COMMENT = '%s'", table.getName(), table.getComment()));
        }

        //查询库中的字段
        private List<TColumn> findOldColumns(String dbName, String tableName) throws SQLException {
            List<Map> columnInfoMaps = execQuerySQL(" SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE,EXTRA,COLUMN_DEFAULT,COLUMN_COMMENT,TABLE_SCHEMA,TABLE_NAME " +
                    " FROM information_schema.columns WHERE TABLE_SCHEMA='" + dbName + "' AND TABLE_NAME='" + tableName + "' ");
            return columnInfoMaps.stream().map(colMap -> new TColumn()
                    .setName((String) colMap.get("COLUMN_NAME"))
                    .setType((String) colMap.get("COLUMN_TYPE"))
                    .setIsNull(colMap.get("IS_NULLABLE").toString().contains("YES"))
                    .setIsAutoIncrement(colMap.get("EXTRA").toString().contains("auto_increment"))
                    .setDefaultVal((String) colMap.get("COLUMN_DEFAULT"))
                    .setComment((String) colMap.get("COLUMN_COMMENT"))
            ).collect(Collectors.toList());
        }

        //查询库中索引
        private List<TIndex> findOldIndexs(String dbName, String tableName) throws SQLException {
            String sqlTemplate = " SELECT TABLE_SCHEMA,TABLE_NAME,NON_UNIQUE,INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME,COLLATION " +
                    " FROM information_schema.statistics " +
                    " WHERE INDEX_SCHEMA = '%s' " +
                    " AND TABLE_NAME = '%s' ";
            List<Map> indexInfoMaps = execQuerySQL(String.format(sqlTemplate, dbName, tableName));

            Map<String, TIndex> name2IndexMap = new LinkedHashMap();

            for(Map indexInfoMap : indexInfoMaps) {
                String keyName = (String) indexInfoMap.get("INDEX_NAME");
                String type = "1".equals(indexInfoMap.get("NON_UNIQUE")) ? DBConstants.INDEX_TYPE_NORMAL : DBConstants.INDEX_TYPE_UNIQUE;
                String columnName = (String) indexInfoMap.get("COLUMN_NAME");

                TIndex tIndex = name2IndexMap.get(keyName);
                if (tIndex == null){
                    tIndex = new TIndex().setName(keyName).setType(type);
                    name2IndexMap.put(keyName, tIndex);
                }

                tIndex.addColumn(columnName);
            }

            return new ArrayList<>(name2IndexMap.values());
        }

        @Override
        public void doAddColumn(String tableName, TColumn column) throws SQLException {
            executeSQL(getAddColumnDDL(tableName, column));
        }

        @Override
        public void doModifyColumn(String tableName, TColumn column) throws SQLException {
            executeSQL(getModifyColumnDDL(tableName, column));
        }

        @Override
        public void dropPrimaryKey(String tableName) throws SQLException {
            TableInfo oldTable = getOldTable(tableName);
            List<String> keys = oldTable.getKeyList();

            //mysql删除主键必须先去除去其【自增】的属性
            List<TColumn> autoIncrementKeys = oldTable.getColumnList().stream().filter(c -> keys.contains(c.getName())).filter(TColumn::getIsAutoIncrement).collect(Collectors.toList());
            for (TColumn autoIncrementKey : autoIncrementKeys) {
                autoIncrementKey.setIsAutoIncrement(false);
                doModifyColumn(tableName, autoIncrementKey);
            }
            executeSQL(String.format("ALTER TABLE `%s` DROP PRIMARY KEY", tableName));
        }

        @Override
        public void addPrimaryKey(String tableName, List<String> keys) throws SQLException {
            executeSQL(String.format(" ALTER TABLE `%s` add PRIMARY KEY (`" + String.join("`, `", keys) + "`) ", tableName));
        }

        @Override
        public void doDropIndex(String tableName, String indexName) throws SQLException {
            executeSQL(String.format("DROP INDEX `%s` ON `%s`", indexName, tableName));
        }

        @Override
        public void doAddIndex(String tableName, TIndex index) throws SQLException {
            executeSQL(String.format("ALTER TABLE `%s` ADD %s ", tableName, mysqlIndexDefine(index)));
        }

        @Override
        public void doModifyIndex(String tableName, TIndex index) throws SQLException {
            doDropIndex(tableName, index.getName());
            doAddIndex(tableName, index);
        }
    }
}